﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.Data.OracleClient;

namespace TracNghiem.Models
{
    public class BaiThiModel
    {
        public string id { get; set; }
        public string user_id { get; set; }
        public string lan_thi { get; set; }
        public string de_thi_id { get; set; }
        public string cau_tl_id { get; set; }
        public string user_status { get; set; }
        public string ngay { get; set; }
        
        public string user_name { get; set; }
        public string ho_ten { get; set; }
        public string cau_dung { get; set; }
        public string tong_so_cau { get; set; }
        public string don_vi { get; set; }

        public void addBai_Thi1(BaiThiModel baithi)
        {
            string sql = "INSERT INTO bai_thi";
            sql += " select nvl((select max(id)+1 from bai_thi ),1)," + baithi.user_id + ","+baithi.lan_thi+",";
            sql += baithi.de_thi_id + "," + baithi.cau_tl_id + "," + baithi.user_status + ",to_date('" + baithi.ngay + "','dd/mm/yyyy') from dual";
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public void addBai_Thi(string user_id,string lan_thi,string de_thi_id,string ngay,string[] cau_tl_id,string[] user_status)
        {
            string sql = "INSERT INTO bai_thi";
            sql += " select nvl((select max(id)+1 from bai_thi ),1)," + user_id + "," + lan_thi + ",";
            sql += de_thi_id + ",to_date('" + ngay + "','dd/mm/yyyy'),CAST(";
            sql += " multiset(select * from(";
            for (int i = 0; i < cau_tl_id.Length; i++)
            {
                sql += " select " + cau_tl_id[i] + " a," + user_status[i] + " b from dual";
                if (cau_tl_id.Length != 1 && i < cau_tl_id.Length - 1)
                    sql += " UNION ";
            }
            sql += " )) as bai_thi_table) from dual";
            
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public string getLan_thi(string de_thi_id,string user_id)
        {
            string sql = "SELECT nvl((select max(lan_thi)+1 from bai_thi where user_id=" + user_id + " and de_thi_id=" + de_thi_id + " ),1) from dual ";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            string lan_thi="";
            while (odr.Read())
            {
                lan_thi = odr[0].ToString();
            }
            General.conn_global.Close();
            return lan_thi;

        }
        public List<BaiThiModel> getTestById(int id)
        {
            List<BaiThiModel> Test = new List<BaiThiModel>();
            OracleDataReader odr = General.Lib_GetDataReader("select * from bc_ketqua");
            while (odr.Read())
            {
                Test.Add(new BaiThiModel
                {
                    don_vi = odr["don_vi"].ToString(),
                    user_name = odr["name"].ToString(),
                    ho_ten = odr["ho_ten"].ToString(),
                    lan_thi = odr["lan_thi"].ToString(),
                    de_thi_id = odr["de_thi_id"].ToString(),
                    cau_dung = odr["dung"].ToString(),
                    tong_so_cau = odr["tong"].ToString()
                });
            }
            General.conn_global.Close();
            return Test;
        }
        public int checkUser_Baithi(string user_id,string de_thi_id)
        {
            string sql = "select id from bai_thi where user_id ='" + user_id + "' and de_thi_id='"+de_thi_id+"'";           
            return General.Lib_CheckExist(sql);
        }

  //        select b.name,a.user_name,a.ho_ten,c.* from
  //(select * from nguoi_dung) a, 
  //(select * from donvi) b,  
  //(select user_id,lan_thi,de_thi_id,sum(dung) dung,sum(tong) tong from
  //(select user_id,lan_thi,de_thi_id,decode(trang_thai,'dung',tong) dung,tong  from
  //(select user_id,lan_thi,de_thi_id,trang_thai,count(*) tong
  //from( select b.*,a.tong_1,decode(tong_2,tong_1,'dung','sai') trang_thai from  
  //(select cau_hoi_id,count(*) tong_1 from cau_traloi
  //group by cau_hoi_id) a,  
  //( select user_id,lan_thi,de_thi_id, cau_hoi_id,count(*) tong_2 from
  //(select b.*,a.status,a.cau_hoi_id from
  // (select * from cau_traloi) a,
  // (SELECT *  FROM bai_thi ) b
  //  where a.id = b.cau_tl_id and user_status=status
  // )group by user_id,lan_thi,de_thi_id, cau_hoi_id) b
  // where a.cau_hoi_id=b.cau_hoi_id)   
  // group by user_id,lan_thi,de_thi_id,trang_thai) 
  // ) group by user_id,lan_thi,de_thi_id) c
  //  where a.id=c.user_id and a.donvi_id=b.id order by user_id,lan_thi,de_thi_id

    //SELECT b.*,a.id, a.user_id, a.lan_thi, a.de_thi_id
    //FROM bai_thi1 a,table(a.ket_qua) b
    }
    
}